﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Pages_chauffeur : System.Web.UI.Page
{
    algemeen alg = new algemeen();
    WebMsgBox ms = new WebMsgBox();
    
    protected void Page_Load(object sender, EventArgs e)
    {
        //if (R_kies_route.Items.Count == 0)
       //     zoek_routes(R_kies_route);
        if (P_kies_transport.Items.Count == 0)
        {
            zoek_routes(P_kies_transport);
            P_kies_pakket.Enabled = false;
        }
        Panel1.Visible = false;
    }

    protected void P_kies_transport_SelectedIndexChanged(object sender, EventArgs e)
    {
        //zoek nu alle bij behorende pakketen in dit transport
        P_kies_pakket.Enabled = true;
        zoek_pakketen(P_kies_pakket, P_kies_transport.SelectedValue.ToString());
    }

    protected void P_kies_route_SelectedIndexChanged(object sender, EventArgs e)
    {
        //kies pakket
    }
    protected void P_kies_pakket_SelectedIndexChanged(object sender, EventArgs e)
    {
        //geef info weer van het pakket
        zoek_pakket_info(P_kies_pakket.SelectedValue.ToString());
    }
    protected void TextBox1_TextChanged(object sender, EventArgs e)
    {
        //geef info weer van het pakket
        zoek_pakket_info(TextBox1.Text);
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        System.Data.SqlClient.SqlConnection sqlConnection1 =
            new System.Data.SqlClient.SqlConnection(alg.sql_string);

        
        System.Data.SqlClient.SqlCommand cmd1 = new System.Data.SqlClient.SqlCommand();
        cmd1.CommandType = System.Data.CommandType.Text;
        System.Data.SqlClient.SqlDataReader lees1;
        string gelukt = "";
        
        string query1 = "DECLARE @return_value int EXEC @return_value = [dbo].[bezorg_pakket] '" + Info_pakket_id.Text + "','" + Info_route.Text + "' SELECT	'Return Value' = @return_value ";
        cmd1.CommandText = query1;
        cmd1.Connection = sqlConnection1;
        sqlConnection1.Open();

        lees1 = cmd1.ExecuteReader();
        lees1.Read();
        gelukt = lees1.GetSqlValue(0).ToString();
        lees1.Close();

        if (gelukt == "1")
        {
            ms.Show("Route succesvol afgerond!");
            if(Info_eind.Text == "True")
                alg.status_update(Info_pakket_id.Text, "12", false);
            else
                alg.status_update(Info_pakket_id.Text, "13", false);
        }
        else
        {
            ms.Show("ERROR");
        }
        zoek_pakketen(P_kies_pakket, P_kies_transport.SelectedValue.ToString());
            
       
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        //niet afgeleverd
    }


    private void zoek_pakket_info(string pakket_id)
    {
        string sql_query =  "select distinct (P.pakket_id), ";
		sql_query += 	    "(RTRIM(Lbron.straatnaam) + ' ' + RTRIM(Lbron.huisnummer) + ' , ' + Lbron.postcode + ' ' + RTRIM(Lbron.plaats)) as best, ";
		sql_query += 	    "(RTRIM(Lbest.straatnaam) + ' ' + RTRIM(Lbest.huisnummer) + ' , ' + Lbest.postcode + ' ' + RTRIM(Lbest.plaats)) as bron ";
        sql_query +=        "from [dbo].[pakket] P, [dbo].[route] Rbron,[dbo].[route] Rbest,[dbo].[locatie] Lbron,[dbo].[locatie] Lbest ";
        sql_query +=        "where P.pakket_id = '" + pakket_id + "' ";
        sql_query += 	    "and P.pakket_id = Rbron.pakket_id  ";
        sql_query += 	    "and P.pakket_id = Rbest.pakket_id ";
        sql_query += 	    "and Rbron.locatie_bron_id = Lbron.locatie_id ";
        sql_query += 	    "and Rbest.locatie_best_id = Lbest.locatie_id ";
        sql_query += 	    "and Rbest.eindbestemming = 1 ";
        sql_query += 	    "and Rbron.volgorde = 0 ";
        using (SqlConnection connection = new SqlConnection(alg.sql_string))
        using (SqlCommand command = new SqlCommand(sql_query, connection))
        {
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Panel1.Visible = true;
                    Info_pakket_id.Text = reader.GetSqlValue(0).ToString();
                    Info_bron.Text = reader.GetSqlValue(1).ToString();
                    Info_best.Text = reader.GetSqlValue(2).ToString();
                    
                    //Info_status.Text = zoek_status(reader.GetSqlValue(0).ToString());
                }
            }
        }

        sql_query =  "select route_id, eindbestemming from [actuele_routes] where pakket_id = '"+pakket_id+"'";
        using (SqlConnection connection = new SqlConnection(alg.sql_string))
        using (SqlCommand command = new SqlCommand(sql_query, connection))
        {
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Info_route.Text = reader.GetSqlValue(0).ToString();
                    Info_eind.Text = reader.GetSqlValue(1).ToString();
                }
            }
        }
    }

    private void zoek_status(string pakket_id)
    {
        string routeid = "";
        string statusid = "";
        string statusnaam = "";
        string sql_query = "EXEC [dbo].[Bepaal_laatste_status] @pakket_id = '" + pakket_id + "'";
        using (SqlConnection connection = new SqlConnection(alg.sql_string))
        using (SqlCommand command = new SqlCommand(sql_query, connection))
        {
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    routeid = reader.GetSqlValue(0).ToString();
                    statusid = reader.GetSqlValue(1).ToString();
                }
            }
        }
        sql_query = "select naam from status where status_id = '" + statusid + "'";
        using (SqlConnection connection = new SqlConnection(alg.sql_string))
        using (SqlCommand command = new SqlCommand(sql_query, connection))
        {
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    statusnaam = reader.GetSqlValue(0).ToString();
                }
            }
        }
        Info_route.Text= routeid;
        //Info_status.Text = statusnaam;
    }


    private void zoek_routes(DropDownList x)
    {
        x.Items.Clear();
        add_leeg_item(x);
        string sql_query = "SELECT transport_id, naam FROM [dbo].[transport]";
        using (SqlConnection connection = new SqlConnection(alg.sql_string))
        using (SqlCommand command = new SqlCommand(sql_query, connection))
        {
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    ListItem keuze = new ListItem();
                    keuze.Text = reader.GetSqlValue(1).ToString();
                    keuze.Value = reader.GetSqlValue(0).ToString();
                    x.Items.Add(keuze);
                }
            }
        }
    }
    

    private void zoek_pakketen(DropDownList x, string transport_ID)
    {
        x.Items.Clear();
        add_leeg_item(x);
        string sql_query = "select * from [actuele_routes] where transport_id = '"+transport_ID+"'";
        using (SqlConnection connection = new SqlConnection(alg.sql_string))
        using (SqlCommand command = new SqlCommand(sql_query, connection))
        {
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    ListItem keuze = new ListItem();
                    keuze.Text = reader.GetSqlValue(1).ToString();
                    keuze.Value = reader.GetSqlValue(1).ToString();
                    x.Items.Add(keuze);

                }
            }
        }
    }

    private void add_leeg_item(DropDownList x){
        ListItem keuze = new ListItem();
        keuze.Text = "maak een keuze";
        keuze.Value = "0";
        x.Items.Add(keuze);
    }


    
}
